package com.fiap.trecho;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.fiap.dao.ConnectionFactory;
import com.fiap.voo.VooBean;

public class TrechoDao {

	public void create(VooBean voo) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		

		try {
			
			sql.append(" INSERT INTO TRECHO_VOO ");
			sql.append(" (ID_VOO, ");
			sql.append("  ORIGEM_TRECHO, ");
			sql.append("  DESTINO_TRECHO, ");
			sql.append("  DT_PREVISTA_DECOLAGEM, ");
			sql.append("  DT_PREVISTA_POUSO) ");
			sql.append(" VALUES ( ?,?,?,?,?) ");
			
			pstmt = con.prepareStatement(sql.toString());

			for (TrechoBean trechoBean : voo.getListaTrechos()) {
				int i=0;
				
				pstmt.setLong(++i, voo.getId());
				pstmt.setString(++i, trechoBean.getOrigemTrecho());
				pstmt.setString(++i, trechoBean.getDestinoTrecho());
				pstmt.setDate(++i, new java.sql.Date(trechoBean.getDataPrevistaDecolagem().getTime()));
				pstmt.setDate(++i, new java.sql.Date(trechoBean.getDataPrevistaPouso().getTime()));

				pstmt.addBatch();
			}
			pstmt.executeBatch();
			

		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			pstmt.close();
			con.close();
		}

	}
	
	public void update(VooBean voo) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();

		try {
			sql.append(" UPDATE TRECHO_VOO ");
			sql.append(" SET ");
			sql.append(" ORIGEM_TRECHO  = ?, ");
			sql.append(" DESTINO_TRECHO = ?, ");
			sql.append(" DT_PREVISTA_DECOLAGEM = ?, ");
			sql.append(" DT_PREVISTA_POUSO = ?, ");
			sql.append(" ATIVO = ? ");
			sql.append(" WHERE ID =? ");

			pstmt = con.prepareStatement(sql.toString());

			for (TrechoBean trechoBean : voo.getListaTrechos()) {
				int i = 0;
				pstmt.setString(++i, trechoBean.getOrigemTrecho());
				pstmt.setString(++i, trechoBean.getDestinoTrecho());
				pstmt.setDate(++i, new java.sql.Date(trechoBean.getDataPrevistaDecolagem().getTime()));
				pstmt.setDate(++i, new java.sql.Date(trechoBean.getDataPrevistaPouso().getTime()));
				pstmt.setLong(++i, trechoBean.isAtivo()?1:0);
				pstmt.setLong(++i, trechoBean.getId());

				pstmt.addBatch();
			}

			pstmt.executeBatch();

		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			pstmt.close();
			con.close();
		}
	}
	
	public void deleteTrecho(TrechoBean trecho) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();

		int i=0;
		try {
			sql.append(" DELETE FROM TRECHO_VOO ");
			sql.append(" WHERE ID_VOO = ? ");
			
			if(trecho.getId()>0){
				sql.append(" AND   ID = ? ");
			}	
			
			pstmt = con.prepareStatement(sql.toString());
			
			pstmt.setLong(++i, trecho.getIdVoo());
			
			if(trecho.getId()>0){
				pstmt.setLong(++i, trecho.getId());
			}
			
			pstmt.executeUpdate();

		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			pstmt.close();
			con.close();
		}
	}
	
	public List<TrechoBean> list(VooBean voo) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		ResultSet rs = null;
		int i=0;
		List<TrechoBean> lista = new ArrayList<TrechoBean>();

		try {
			sql.append(" SELECT * FROM  TRECHO_VOO ");
			sql.append(" WHERE ID_VOO = ? ");
			sql.append(" ORDER BY ID ");
			
			
			pstmt = con.prepareStatement(sql.toString());
			
			pstmt.setLong(++i, voo.getId());
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				lista.add(this.mountBean(rs));
			}
			

		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			pstmt.close();
			con.close();
			rs.close();
		}
		return lista;
	}
	
	public TrechoBean mountBean(ResultSet rs) throws SQLException{
		TrechoBean v = new TrechoBean();
		v.setId(rs.getLong("ID"));
		v.setIdVoo(rs.getLong("ID_VOO"));
		v.setDataPrevistaDecolagem(rs.getDate("DT_PREVISTA_DECOLAGEM"));
		v.setDataPrevistaPouso(rs.getDate("DT_PREVISTA_POUSO"));
		v.setDestinoTrecho(rs.getString("DESTINO_TRECHO"));
		v.setOrigemTrecho(rs.getString("ORIGEM_TRECHO"));
		v.setAtivo(rs.getBoolean("ATIVO"));
		
		return v;
	}
}
